package ar.edu.itba.it.paw.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


import ar.edu.itba.it.paw.dao.ProductDAO;
import ar.edu.itba.it.paw.domain.Category;
import ar.edu.itba.it.paw.domain.ConnectionManager;
import ar.edu.itba.it.paw.domain.Product;
import ar.edu.itba.it.paw.domain.Transaction;
import ar.edu.itba.it.paw.domain.User;
import ar.edu.itba.it.paw.domain.exceptions.DatabaseException;


public class DatabaseProductDAO implements ProductDAO {

	private static ProductDAO instance;
	
	public static synchronized ProductDAO getInstance(){
		if(instance == null)
			instance = new DatabaseProductDAO();
		return instance;
	}
	
	public ArrayList<Product> getAllProducts(){
		ConnectionManager manager = ConnectionManager.getInstance();
		
		Connection conn = manager.getConnection();
		PreparedStatement stm;
		ArrayList<Product> products = new ArrayList<Product>();
		
		try {
			String query = "SELECT * FROM \"Articulo\",\"articulo_categoria\",\"Categoria\",\"Usuario\" WHERE \"articuloId\" = \"articulo_id\" and \"categoryId\" = \"categoria_id\" and cantidad > 0 and \"Usuario\".\"userId\" = \"Articulo\".\"userId\"";
			stm = conn.prepareStatement(query); 
			
			ResultSet results = stm.executeQuery();
			//Given that Article and User tables have both a column name, we use their position to get the information
			while (results.next()) {
				Category categ = new Category(results.getString("name"));
				categ.setId(results.getInt("categoria_id"));
				User user = new User(results.getString("userName"),results.getString("password"),results.getString(10),results.getString("apellido"),results.getString("mail"));
				user.setId(results.getInt("userId"));
				Product product = new Product(results.getString(1), results.getString("descripcion"), results.getDate("fechaPublicacion"),
						 categ,results.getFloat("precioUnitario"), results.getInt("cantidad"), results.getBoolean("estado"), user);
				product.setId(results.getInt("articuloId"));
				products.add(product);
			}
		} catch (SQLException e) {
			throw new DatabaseException();
		}
		manager.closeConnection();
		return products;
	}
	
	public Product getProduct(int id){
		ConnectionManager manager = ConnectionManager.getInstance();
		Connection conn = manager.getConnection();
		PreparedStatement stm;
		Product product = null;
		try {
			stm = conn.prepareStatement("SELECT * FROM \"Articulo\",\"articulo_categoria\",\"Categoria\",\"Usuario\" WHERE \"articuloId\" = \"articulo_id\" and \"categoryId\" = \"categoria_id\" and \"articuloId\" = ? and \"Usuario\".\"userId\" = \"Articulo\".\"userId\" ");
			stm.setInt(1,id);
			
			ResultSet results = stm.executeQuery();
			if(results.next()){
				Category categ = new Category(results.getString("name"));
				categ.setId(results.getInt("categoria_id"));
				User user = new User(results.getString("userName"),results.getString("password"),results.getString(10),results.getString("apellido"),results.getString("mail"));
				user.setId(results.getInt("userId"));
				product = new Product(results.getString(1), results.getString("descripcion"),results.getDate("fechaPublicacion") ,categ ,results.getFloat("precioUnitario"), 
				results.getInt("cantidad"), results.getBoolean("estado"), user);
				product.setId(results.getInt("articuloId"));
			}
		} catch (SQLException e) {
			throw new DatabaseException();
		}
		manager.closeConnection();
		return product;
	}
	
	public List<Product> getProductsByCategory(Category category){
		ConnectionManager manager = ConnectionManager.getInstance();
		Connection conn = manager.getConnection();
		PreparedStatement stmt;
		List<Product> product_list = new ArrayList<Product>();
		try{
			stmt = conn.prepareStatement("select * from \"Articulo\", \"Articulo_categoria\", \"Usuario\" where \"articuloId\" = \"articulo_id\" and \"Usuario\".\"userId\" = \"Articulo\".\"userId\" and \"category_id\" = ? ");
			stmt.setInt(1, category.getId());
			ResultSet results = stmt.executeQuery();
			if(results.next()){
				Category categ = new Category(results.getString("name"));
				categ.setId(results.getInt("categoria_id"));
				User user = new User(results.getString("userName"),results.getString("password"),results.getString(10),results.getString("apellido"),results.getString("mail"));
				user.setId(results.getInt("userId"));
				Product product = new Product(results.getString(1), results.getString("descripcion"),results.getDate("fechaPublicacion") ,categ ,results.getFloat("precioUnitario"), 
						results.getInt("cantidad"), results.getBoolean("estado"), user);
				product.setId(results.getInt("articuloId"));
				product_list.add(product);
			}
		} catch(SQLException e){
			throw new DatabaseException();
		}
		manager.closeConnection();
		return product_list;
	}
	
	public void addProduct(Product product,User owner){
		
		ConnectionManager manager = ConnectionManager.getInstance();
		Connection conn = manager.getConnection();
		PreparedStatement stmt;
		try{
			stmt = conn.prepareStatement("insert into \"Articulo\"(nombre,descripcion,\"precioUnitario\",cantidad,\"fechaPublicacion\",\"userId\",estado) values(?,?,?,?,CURRENT_DATE,?,?)");
			stmt.setString(1,product.getName());
			stmt.setString(2, product.getDescription());
			stmt.setFloat(3,product.getPrice());
			stmt.setInt(4, product.getQuantity());
			stmt.setInt(5, owner.getId());
			stmt.setBoolean(6, product.getState());
			stmt.execute();
			
		}catch (SQLException e){
			throw new DatabaseException();
		}
		Integer id = -1;
		PreparedStatement stmt1;
		try{
			stmt1 = conn.prepareStatement("select max(\"articuloId\") as max from \"Articulo\" ");
			ResultSet myCursor = stmt1.executeQuery();
			if(myCursor.next()){
				id = myCursor.getInt("max");
			}
		}catch(SQLException e){
			throw new DatabaseException();
		}
		
		
		PreparedStatement stmt2;
		try{
			stmt2 = conn.prepareStatement("insert into \"articulo_categoria\"(articulo_id,categoria_id) values(?,?)");
			stmt2.setInt(1, id);
			stmt2.setInt(2, product.getCategory().getId());
			stmt2.execute();
		}catch (SQLException e){
			throw new DatabaseException();
		}
		
		manager.closeConnection();
	}
	
	public void editProduct(Product product){
		ConnectionManager manager = ConnectionManager.getInstance();
		Connection conn = manager.getConnection();
		PreparedStatement stm;
		try {
			stm = conn.prepareStatement("UPDATE \"Articulo\" SET \"nombre\" = ? , \"descripcion\" = ? , \"estado\" = ?  WHERE \"articuloId\" = ?");
			stm.setString(1, product.getName());
			stm.setString(2, product.getDescription());
			stm.setBoolean(3, product.getState());
			stm.setInt(4, product.getId());
			stm.execute();
		} catch (SQLException e) {
			throw new DatabaseException();
		}
		
		PreparedStatement stm1;
		try {
			stm1 = conn.prepareStatement("UPDATE \"articulo_categoria\" SET \"categoria_id\" = ?  WHERE \"articulo_id\" = ?");
			stm1.setInt(1, product.getCategory().getId());
			stm1.setInt(2, product.getId());
			stm1.execute();
		} catch (SQLException e) {
			throw new DatabaseException();
		}
		manager.closeConnection();
	}
	
	public void buy(Transaction transaction){
		ConnectionManager manager = ConnectionManager.getInstance();
		Connection conn = manager.getConnection();
		PreparedStatement stm;
		try {
			stm = conn.prepareStatement("UPDATE \"Articulo\" SET \"cantidad\" = ? WHERE \"articuloId\" = ?");
			stm.setInt(1, transaction.getProduct().getQuantity() - transaction.getQuantity());
			stm.setInt(2, transaction.getProduct().getId());
			stm.execute();
		} catch (SQLException e) {
			throw new DatabaseException();
		}
		manager.closeConnection();
	}
	
	public void deleteProduct(Product product){
		ConnectionManager manager = ConnectionManager.getInstance();
		Connection conn = manager.getConnection();
		PreparedStatement stm;
		try {
			stm = conn.prepareStatement("DELETE FROM \"Articulo\" WHERE \"articuloId\" = ? ");
			stm.setInt(1, product.getId());
			stm.execute();
		} catch (SQLException e) {
			throw new DatabaseException();
		}
		manager.closeConnection();
	}
	
	public void addTransaction(Transaction transaction){
		ConnectionManager manager = ConnectionManager.getInstance();
		Connection conn = manager.getConnection();
		PreparedStatement stmt;
		try{
			stmt = conn.prepareStatement("Insert into \"Transaccion\"(\"compradorId\",\"vendedorId\",\"articuloId\",cantidad,fecha) values(?,?,?,?,CURRENT_DATE)");
			stmt.setInt(1, transaction.getBuyer().getId());
			stmt.setInt(2, transaction.getSeller().getId());
			stmt.setInt(3, transaction.getProduct().getId());
			stmt.setInt(4, transaction.getQuantity());
			stmt.execute();
		}catch(SQLException e){
			throw new DatabaseException();
		}
		manager.closeConnection();
	}

}
